<?php

require_once('Vega/Dao/Table.php');

class Mobitv_Admin_AdminDao extends Vega_Dao_Table {

    public function isSubCp($cpId) {
        $cpId = intval($cpId);
        $sql = "SELECT parent_id FROM admin WHERE id=$cpId AND parent_id>0";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();

        return $row ? $row['parent_id'] : false;
    }

    // Chi goi khi da so khop hash cookie
    public function tryLoginCookie($userid, $username) {
        $userid = intval($userid);
        $_username = $this->_db->valueQuote($username);
        $sql = "SELECT admin.id, ag.id as groupid, ag.video_viewall as viewall FROM admin LEFT JOIN admin_group ag ON admin.group_id = ag.id
                WHERE admin.name=$_username AND admin.id=$userid";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        if ($row) {
            $_SESSION['userid'] = $row['id'];
            $_SESSION['groupid'] = $row['groupid'];
            $_SESSION['viewall'] = $row['viewall'];
            $_SESSION['username'] = $username;

            return array($row['id'], $row['groupid'], $row['viewall']);
        } else {
            return false;
        }
    }

    public function isAuthenticated($username, $password) {
        $config = Vega_App::getInstance()->coreConfig;
        $username = $this->valueQuote($username);
        $passwordMD5 = $this->valueQuote(md5($password));
        $sql = "SELECT admin.id, ag.id as groupid, admin.inactive, ag.video_viewall as viewall, ag.abbr FROM admin LEFT JOIN admin_group ag ON admin.group_id = ag.id
                WHERE admin.name=$username AND admin.password=$passwordMD5";
        //$sql .= " AND ((is_logged_in = 0) OR (is_logged_in = 1 AND (TIMESTAMPDIFF(SECOND ,last_activity, now()) > " . $config['admin_session']['session_timeout'] . ")))";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        if ($row) {
            $sql_logged = "UPDATE admin SET last_activity = now(), is_logged_in = 1 WHERE id = " . $row['id'];
            try {
                $this->_db->queryWrite($sql_logged);
            } catch (Exception $e) {
                ;
            }
            return array($row['id'], $row['groupid'], $row['viewall'], $row['abbr'], $row['inactive']);
        }
        else
            return false;
    }

    public function isExist($username) {
        $username = $this->_db->valueQuote($username);

        $sql = "SELECT id FROM admin WHERE name=$username";
        $this->_db->query($sql);

        return $this->_db->fetchRow();
    }

    public function getDetail($userId) {
        $userId = intval($userId);
        $sql = "SELECT * FROM admin WHERE id=$userId";
        $this->_db->query($sql);
        return $this->_db->fetchRow();
    }

    public function getAssignmentInfo($userId) {
        $userId = intval($userId);
        $sql = "SELECT * FROM mem_assignment WHERE member_id=$userId ";
        $this->_db->query($sql);

        return $this->_db->fetchRow();
    }

    public function getList($limit, $page, $group_admin = '', $deleted = 0, $txtSearch = '') {
        $txtSearch = mysql_escape_string($txtSearch);
        $offset = $limit * ($page - 1);
        $_limit = ' LIMIT ' . $limit;
        $_offset = ' OFFSET ' . $offset;
        $sql = "SELECT distinct a.* FROM admin a ";
        $sql .= " LEFT JOIN admin_access_role r ON r.admin_id = a.id ";
        $sql .= " LEFT JOIN admin_group ag ON r.group_id = ag.id ";
        $sql .= " WHERE a.inactive = $deleted ";
        if ($group_admin != '') {
            $sql .= " AND ag.id = $group_admin";
        }
        if ($txtSearch != '') {
            $sql .= " AND (a.fullname like '%$txtSearch%'";
            $sql .= " OR a.name like '%$txtSearch%')";
        }
        $sql .= $_limit . $_offset;
        $this->_db->query($sql);
        $arrResult = $this->_db->fetchRowset();
        return $arrResult;
    }

    public function getCpList() {
        $sql = "SELECT a.* FROM admin a INNER JOIN admin_group ag ON a.group_id=ag.id WHERE ag.abbr='cp'";
        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    public function getTvCpList() {
        $sql = "SELECT a.* FROM admin a INNER JOIN admin_group ag ON a.group_id=ag.id WHERE ag.abbr='tv_cp'";
        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    public function getSubCpList($cpId) {
        $cpId = intval($cpId);
        $sql = "SELECT admin.* FROM admin WHERE parent_id=$cpId";

        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    // Dem so nguoi dung
    public function countAll($group_admin = '', $deleted = 0, $txtSearch = '') {
        $sql = "SELECT COUNT(a.id) as numrow
                FROM admin a, admin_access_role r WHERE a.id = r.admin_id AND a.inactive = $deleted ";
        if ($group_admin != '') {
            $sql .= " AND r.group_id = $group_admin";
        }
        if ($txtSearch != '') {
            $sql .= " AND (a.fullname like '%$txtSearch%'";
            $sql .= " OR a.name like '%$txtSearch%')";
        }
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();
        return $row['numrow'];
    }

    // Set quyen
    public function setPerm($userId, $action, $perm) {
        $userId = intval($userId);
        $perm = intval($perm);
        $action = $this->_db->valueQuote($action);

        $sql = "INSERT INTO admin_role SET admin_id=$userId, action_id=(SELECT id FROM admin_action where name=$action), permission=$perm ON DUPLICATE KEY UPDATE permission=$perm";

        return $this->_db->queryWrite($sql);
    }

    // Nguoi dung moi
    public function insert($data) {
        $groupIdList = trim($data['group_id']);
        $name = $this->_db->valueQuote($data['name']);
        $passwordMd5 = $this->_db->valueQuote(md5($data['password']));
        $fullname = $this->_db->valueQuote($data['fullname']);
        $email = $this->_db->valueQuote($data['email']);
        $parent_id = intval($data['parent_id']);
        $group_id = intval($groupIdList);
        $sql = "INSERT INTO admin SET name=$name, group_id=$group_id, fullname=$fullname, email=$email, password=$passwordMd5, created_by=" . $_SESSION['userid'] . ", parent_id = $parent_id, created_time=NOW()";

        if ($this->_db->queryWrite($sql)) {
            $sql = "SELECT max(id) AS id FROM admin WHERE created_by=" . $_SESSION['userid'];
            $this->_db->query($sql, 'master');
            if (!$newItem = $this->_db->fetchRow())
                return false;
            $userId = $newItem['id'];
            $sql = "INSERT INTO mem_assignment SET member_id=$userId, encode_group=1";
            try {
                $this->_db->queryWrite($sql);
            } catch (Exception $e) {
                ;
            }
            $sql_group_id = "INSERT INTO admin_access_role(`admin_id`,`group_id`,`created_by`,`created_time`) VALUES ";
            $arrGroupId = explode('!#!', $groupIdList);
            for ($i = 0; $i < sizeof($arrGroupId); $i++) {
                $sql_group_id .= " ($userId, $arrGroupId[$i]," . $_SESSION['userid'] . ", now()),";
            }
            try {
                $this->_db->queryWrite(substr($sql_group_id, 0, -1));
            } catch (Exception $e) {
                ;
            }
            return true;
        }else
            return false;
    }

    // CP con moi
    public function insertSubCp($data) {
        $parentId = intval($data['parent_id']);
        $name = $this->_db->valueQuote($data['name']);
        $passwordMd5 = $this->_db->valueQuote(md5($data['password']));
        $fullname = $this->_db->valueQuote($data['fullname']);
        $email = $this->_db->valueQuote($data['email']);

        $sql = "INSERT INTO admin SET name=$name, fullname=$fullname, email=$email, password=$passwordMd5, group_id=2, parent_id=$parentId, created_by=" . $_SESSION['userid'] . ", abbr='cp', created_time=NOW()";

        return $this->_db->queryWrite($sql);
    }

    public function getAddedSubCpId($cpId) {
        $cpId = intval($cpId);
        $sql = "SELECT id FROM admin WHERE parent_id=$cpId ORDER BY created_time DESC LIMIT 1";
        $this->_db->query($sql);
        $row = $this->_db->fetchRow();

        return $row ? $row['id'] : 0;
    }

    public function save($userId, $data) {
        $userId = intval($userId);
        $groupIdList = trim($data['group_id']);
        $fullname = $this->_db->valueQuote($data['fullname']);
        $email = $this->_db->valueQuote($data['email']);
        $parent_id = (isset($data['parent_id'])) ? intval($data['parent_id']) : 0;
        $group_id = intval($groupIdList);
        if (empty($data['password']))
            $sql = "UPDATE admin SET fullname=$fullname, group_id=$group_id, email=$email, parent_id = $parent_id WHERE id=$userId";
        else {
            $passwordMd5 = $this->_db->valueQuote(md5($data['password']));
            $sql = "UPDATE admin SET fullname=$fullname, group_id=$group_id, email=$email, password=$passwordMd5, parent_id = $parent_id WHERE id=$userId";
        }
        $delete_group = "DELETE FROM admin_access_role WHERE admin_id = $userId";
        try {
            $this->_db->queryWrite($delete_group);
        } catch (Exception $e) {
;
        }
        $sql_group_id = "INSERT INTO admin_access_role(`admin_id`,`group_id`,`created_by`,`created_time`) VALUES ";
        $arrGroupId = explode('!#!', $groupIdList);
        for ($i = 0; $i < sizeof($arrGroupId); $i++) {
            $sql_group_id .= " ($userId, $arrGroupId[$i]," . $_SESSION['userid'] . ", now()),";
        }
        try {
            $this->_db->queryWrite(substr($sql_group_id, 0, -1));
        } catch (Exception $e) {
;
        }

        return $this->_db->queryWrite($sql);
    }

    public function saveSubCp($userId, $data) {
        $userId = intval($userId);
        $fullname = $this->_db->valueQuote($data['fullname']);
        $email = $this->_db->valueQuote($data['email']);
        if (empty($data['password']))
            $sql = "UPDATE admin SET fullname=$fullname, email=$email, updated_time=now() WHERE id=$userId";
        else {
            $passwordMd5 = $this->_db->valueQuote(md5($data['password']));
            $sql = "UPDATE admin SET fullname=$fullname, email=$email, password=$passwordMd5, updated_time=now() WHERE id=$userId";
        }

        return $this->_db->queryWrite($sql);
    }

    public function updateDetail($data, $userId) {
        $fullname = $this->_db->valueQuote($data['fullname']);
        $email = $this->_db->valueQuote($data['email']);

        if (empty($data['password'])) {
            // Cap nhat thong tin
            $sql = "UPDATE admin SET fullname=$fullname, email=$email WHERE id=$userId";
        } else {
            // Cap nhat ca thong tin la mat khau
            $passwordMD5 = $this->_db->valueQuote(md5($data['password']));

            $sql = "UPDATE admin SET fullname=$fullname, email=$email, password=$passwordMD5 WHERE id=$userId";
        }
        return $this->_db->queryWrite($sql);
    }

    public function getPermission($userId, $action) {
        $userId = intval($userId);
        $action = $this->_db->valueQuote($action);

        $sql = "SELECT admin.id, admin.group_id, admin_group_role.permission AS group_permission, admin_role.permission AS user_permission
            from admin
            LEFT JOIN (SELECT * FROM admin_action LEFT JOIN admin_group_role ON admin_action.id=admin_group_role.action_id
            WHERE admin_action.name=$action) AS admin_group_role ON admin.group_id=admin_group_role.group_id
            LEFT JOIN (SELECT * FROM admin_action LEFT JOIN admin_role ON admin_action.id=admin_role.action_id WHERE admin_role.admin_id=$userId AND admin_action.name=$action) AS admin_role ON admin.id=admin_role.admin_id
            WHERE admin.id=$userId";
        $this->_db->query($sql);
        return $this->_db->fetchRow();
        /*
          $sql_role = "SELECT role.admin_id, role.group_id, admin_group_role.permission AS group_permission, admin_role.permission AS user_permission
          FROM admin_access_role role
          INNER JOIN admin ON admin.id = role.admin_id
          LEFT JOIN (SELECT * FROM admin_action LEFT JOIN admin_group_role ON admin_action.id=admin_group_role.action_id
          WHERE admin_action.name=$action) AS admin_group_role ON role.group_id=admin_group_role.group_id
          LEFT JOIN (SELECT * FROM admin_action LEFT JOIN admin_role ON admin_action.id=admin_role.action_id WHERE admin_role.admin_id=$userId AND admin_action.name=$action) AS admin_role ON admin.id=admin_role.admin_id
          WHERE admin.id=$userId";
          $this->_db->query($sql_role);
          $arrRole = $this->_db->fetchRowset();
          return $arrRole;
         */
    }

    public function getAdminPermission($userId, $action) {
        $userId = intval($userId);
        $action = $this->_db->valueQuote($action);
        $sql = "SELECT ar.permission FROM admin_role ar INNER JOIN admin_action aa ON aa.id=ar.action_id WHERE ar.admin_id=$userId AND aa.name=$action";
        $this->_db->query($sql);
        echo $sql;
        $row = $this->_db->fetchRow();

        return $row ? $row['permission'] : 0;
    }

    public function getPermissionList($userId) {
        $userId = intval($userId);
        $sql = "SELECT * FROM admin_action LEFT JOIN (SELECT * FROM admin_role WHERE admin_id=$userId) AS admin_role ON admin_action.id=admin_role.action_id";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function getTransactionList($fromDate, $toDate) {
        $fromDate = $this->_db->valueQuote($fromDate);
        $toDate = $this->_db->valueQuote($toDate . " 23:59:59");

        $sql = "SELECT at.*, a.fullname AS admin FROM admin_transaction at INNER JOIN admin a ON at.admin_id=a.id WHERE at.created_time>=$fromDate AND at.created_time<=$toDate";
        $this->_db->query($sql);

        return $this->_db->fetchRowset();
    }

    public function savePermission($userId, $actionId, $perm) {
        $userId = intval($userId);
        $actionId = intval($actionId);
        $perm = intval($perm);

        $sql = "INSERT INTO admin_role SET permission=$perm, admin_id=$userId, action_id=$actionId ON DUPLICATE KEY UPDATE permission=$perm";

        return $this->_db->queryWrite($sql);
    }

    public function deletePermission($userId, $actionId) {
        $userId = intval($userId);
        $actionId = intval($actionId);

        $sql = "DELETE FROM admin_role WHERE admin_id=$userId AND action_id=$actionId";

        return $this->_db->queryWrite($sql);
    }

    public function deleteFromList($list) {
        $list = "(" . implode(",", $list) . ")";
        //$sql = "DELETE FROM admin WHERE deletable=1 AND id IN $list";        
        $sql = "UPDATE admin SET inactive = 1 - inactive WHERE deletable=1 AND id IN $list";
        if ($this->_db->queryWrite($sql)) {
            //$sql = "DELETE FROM mem_assignment WHERE member_id IN $list";
            //$this->_db->queryWrite($sql);
            return true;
        }
        else
            return false;
    }

    public function deleteSubCpFromList($list) {
        $list = "(" . implode(",", $list) . ")";

        $sql = "DELETE FROM admin WHERE id IN $list";

        return $this->_db->queryWrite($sql);
    }

    public function updateLogAdmin($admin_id, $object, $object_id_list, $action) {
        if (!$admin_id)
            return;
        $ip_address = $_SERVER['REMOTE_ADDR'];
        $arr_object = explode(',', $object_id_list);
        if (sizeof($arr_object) > 1) {
            $values = 'VALUES';
            for ($i = 0; $i < sizeof($arr_object); $i++) {
                $values .= "($admin_id, '$object', $arr_object[$i], '$action', '$ip_address', now()),";
            }
            $sql_session = "INSERT INTO admin_session(admin_id,`object`,object_id,`action`,ip_address,created_time) ";
            $sql_session .= substr(trim($values), 0, -1);
        } else {
            $sql_session = "INSERT INTO admin_session(admin_id,`object`,object_id,`action`,ip_address,created_time) ";
            $sql_session .= " VALUES($admin_id, '$object', $object_id_list, '$action', '$ip_address', now())";
        }
        $sql_logged = "UPDATE admin SET last_activity = now() WHERE id = '$admin_id'";
        try {
            $this->_db->queryWrite($sql_session);
        } catch (Exception $e) {
;
        }
        try {
            $this->_db->queryWrite($sql_logged);
        } catch (Exception $e) {
;
        }
    }

    public function updateLoginStatus() {
        if (isset($_SESSION['userid'])) {
            $sql_logged = "UPDATE admin SET last_activity = now(), is_logged_in = 1 WHERE id = " . $_SESSION['userid'];
            try {
                $this->_db->queryWrite($sql_logged);
            } catch (Exception $e) {
                ;
            }
        }
    }

    public function updateLogoutStatus() {
        if (isset($_SESSION['userid'])) {
            $sql_logged = "UPDATE admin SET is_logged_in = 0 WHERE id = " . $_SESSION['userid'];
            try {
                $this->_db->queryWrite($sql_logged);
            } catch (Exception $e) {
                ;
            }
        }
    }

    public function getGroupList($userid = '', $option = 0) {
        $sql = "SELECT role.*, g.name  FROM admin_access_role role, admin_group g WHERE role.group_id = g.id ";
        if ($userid != '') {
            $sql .= " AND role.admin_id = $userid";
        }
        if ($option == 1) {
            $sql .= " GROUP BY g.id ";
        }
        $this->_db->query($sql);
        return $this->_db->fetchRowset();
    }

    public function updateGroupForAdmin($adminid, $grouplist) {
        $arrGroup = explode(',', $grouplist);
        $sql_delete = "DELETE FROM admin_access_role WHERE admin_id = $adminid";
        try {
            $this->_db->queryWrite($sql_delete);
        } catch (Exception $e) {
            ;
        }
        $sql = "INSERT INTO admin_access_role(`admin_id`,`group_id`,`created_by`,`created_time`) VALUES ";
        for ($i = 0; $i < sizeof($arrGroup); $i++) {
            $sql .= " ($adminid, $arrGroup[$i]," . $_SESSION['userid'] . ", now()),";
        }
        try {
            return $this->_db->queryWrite(substr($sql, 0, -1));
        } catch (Exception $e) {
            ;
        }
    }

    public function getAllPermissionAmin($admin_id) {
        $sql = "SELECT p.action_id, p.permission FROM admin_group_role p, admin_access_role c, admin a ";
        $sql .= " WHERE p.group_id = c.group_id AND a.id = c.admin_id ";
        if ($admin_id != '') {
            $sql .= " AND a.id = $admin_id";
        }
        $this->_db->query($sql);
        try {
            $arrResult = $this->_db->fetchRowset();
        } catch (Exception $e) {
            ;
        }
        $sql = "SELECT p.action_id, p.permission FROM admin_role p ";
        $sql .= " WHERE 1=1 ";
        if ($admin_id != '') {
            $sql .= " AND p.admin_id = $admin_id";
        }
        $this->_db->query($sql);
        try {
            $arrResult2 = $this->_db->fetchRowset();
        } catch (Exception $e) {
            ;
        }
        $size = sizeof($arrResult);
        for ($i = 0; $i < sizeof($arrResult2); $i++) {
            $k = $size + $i;
            $arrResult[$k]['action_id'] = $arrResult2[$i]['action_id'];
            $arrResult[$k]['permission'] = $arrResult2[$i]['permission'];
        }
        return $arrResult;
    }

    public function getAllParentCP() {
        $sql = "SELECT * FROM admin WHERE group_id=2 AND parent_id=0 ";
        $this->_db->query($sql);
        $arrResult = $this->_db->fetchRowset();
        return $arrResult;
    }

    public function getGroupAdminUser($username, $password) {
        $password = md5($password);
        $username = $this->_db->valueQuote($username);
        $password = $this->_db->valueQuote($password);
        $sql = "SELECT * FROM admin a, admin_group ag WHERE a.name=$username AND a.password=$password AND a.group_id=ag.id AND ag.abbr='admin'";
        $this->_db->query($sql);
        $arrResult = $this->_db->fetchRow();
        return $arrResult;
    }

    public function updateTB3GAndMobitv(){
        $sql = "select msisdn from transaction where action ='subscribe3G' and content ='3G' and msisdn in (select msisdn from transaction where action='subscribe' and content='2')";
        $this->_db->query($sql);
        $arrResult = $this->_db->fetchRowSet();
        foreach ($arrResult as $user) {
            $msisdn = $user['msisdn'];
            $sql = "UPDATE transaction SET content='2' WHERE msisdn='$msisdn' AND action ='subscribe3G' and content ='3G'";
            $this->_db->queryWrite($sql);
        }
        echo $sql . '<br>';
        /**
        $sql = "select msisdn from transaction where action ='subscribe3G' and content ='3G' and msisdn in (select msisdn from transaction where action='subscribe' and content='1')";
        $this->_db->query($sql);
        $arrResult = $this->_db->fetchRowSet();
        foreach ($arrResult as $user) {
            $msisdn = $user['msisdn'];
            $sql = "UPDATE transaction SET content='1' WHERE msisdn='$msisdn' AND action ='subscribe3G' and content ='3G'";
            $this->_db->queryWrite($sql);
        }
        echo $sql . '<br>';
        /**/
    }

}

?>